#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

ADS_WIRELESS_ENTRY_ANALYSIS_RESULT="
INSERT OVERWRITE TABLE 09_shop_db.wireless_entry_analysis_result
SELECT
    dt,
    entry_point,
    unique_visitor_count AS visitor_num,

    CONCAT(ROUND((unique_visitor_count / SUM(unique_visitor_count) OVER (PARTITION BY dt)) * 100, 2), '%') AS visitor_proportion,

    CONCAT(ROUND((is_order / SUM(is_order) OVER (PARTITION BY dt)) * 100, 2), '%') AS order_buyer_proportion,

    CONCAT(ROUND((is_order / unique_visitor_count) * 100, 2), '%') AS conversion_rate
FROM
    09_shop_db.wireless_entry_analysis
ORDER BY
    dt,
    unique_visitor_count DESC;
"

ADS_PAGE_TYPE_STATS_RESULT="
INSERT INTO 09_shop_db.page_type_stats_result
SELECT
    page_type,
    unique_visitor_count,
    CONCAT(ROUND(unique_visitor_count / total_visitors * 100, 2), '%') AS visitor_ratio
FROM (

         WITH TotalStats AS (
             SELECT SUM(unique_visitor_count) AS total_visitors
             FROM 09_shop_db.wireless_data
         ),
              PageTypeStats AS (
                  SELECT
                      page_type,
                      SUM(unique_visitor_count) AS unique_visitor_count
                  FROM 09_shop_db.wireless_data
                  GROUP BY page_type
              )
         SELECT * FROM PageTypeStats pts, TotalStats ts
     ) subquery
ORDER BY unique_visitor_count DESC;
"

ADS_PAGE_DETAIL_STATS_RESULT="
INSERT INTO 09_shop_db.page_detail_stats_result
SELECT
        page_type AS `页面类型`,
        SUM(page_view_count) AS `浏览量`,
        SUM(unique_visitor_count) AS `访客数`,
        ROUND(AVG(stay_duration), 2) AS `平均停留时长`
    FROM
        09_shop_db.wireless_data
    GROUP BY
        page_type
ORDER BY
    `浏览量` DESC
LIMIT 10;


SELECT
page_type as `页面类型`,
page_view_count AS `浏览量`,
unique_visitor_count AS `访客数`,
stay_duration AS `平均停留时长`
FROM   09_shop_db.page_detail_stats_result;
"

ADS_SOURCE_STATS_RESULT="
INSERT INTO 09_shop_db.source_stats_result
SELECT
    ss.`来源`,
    ss.`访客数`,
    CONCAT(ROUND(ss.`访客数` / ts.total_visitors * 100, 2), '%') AS `访客数占比`
FROM (
         SELECT
             page_type AS `来源`,
             SUM(unique_visitor_count) AS `访客数`
         FROM 09_shop_db.wireless_data
         WHERE dt = '2025-01-01'
         GROUP BY page_type
     ) ss
         CROSS JOIN (
    SELECT
        SUM(`访客数`) AS total_visitors
    FROM (
             SELECT
                 page_type AS `来源`,
                 SUM(unique_visitor_count) AS `访客数`
             FROM 09_shop_db.wireless_data
             WHERE dt = '2025-01-01'
             GROUP BY page_type
         )
) ts
ORDER BY ss.`访客数` DESC;
"

ADS_DESTINATION_STATS_RESULT="
INSERT INTO 09_shop_db.destination_stats_result
SELECT
    ds.`去向`,
    ds.`访客数`,
    CONCAT(ROUND(ds.`访客数` / ts2.total_visitors * 100, 2), '%') AS `访客数占比`,
    ds.`支付金额`,
    CONCAT(ROUND(ds.`支付金额` / ts2.total_amount * 100, 2), '%') AS `支付金额占比`
FROM (

         SELECT
             page_type AS `去向`,
             SUM(unique_visitor_count) AS `访客数`,
             SUM(CASE WHEN is_order = '1' THEN order_amount ELSE 0 END) AS `支付金额`,
             COUNT(*) AS `总记录数`
         FROM 09_shop_db.wireless_data
         WHERE dt = '2025-01-01'
         GROUP BY page_type
     ) ds
         CROSS JOIN (

    SELECT
        SUM(`访客数`) AS total_visitors,
        SUM(`支付金额`) AS total_amount
    FROM (
             SELECT
                 page_type AS `去向`,
                 SUM(unique_visitor_count) AS `访客数`,
                 SUM(CASE WHEN is_order = '1' THEN order_amount ELSE 0 END) AS `支付金额`,
                 COUNT(*) AS `总记录数`
             FROM 09_shop_db.wireless_data
             WHERE dt = '2025-01-01'
             GROUP BY page_type
         )
) ts2
ORDER BY ds.`访客数` DESC;
"


ads_pc_source_analysis_result="
INSERT INTO 09_shop_db.pc_source_analysis_result
SELECT
    ss.`来源页面`,
    ss.`访客数`,
    CONCAT(ROUND(ss.`访客数` / ts.total_visitors * 100, 2), '%') AS `来源占比`
FROM (

         SELECT
             page_type AS `来源页面`,
             SUM(unique_visitor_count) AS `访客数`,
             COUNT(*) AS `记录数`
         FROM 09_shop_db.cleaned_pc_data
         WHERE dt = '2025-01-01'
         GROUP BY page_type
     ) ss
         CROSS JOIN (

    SELECT
        SUM(`访客数`) AS total_visitors
    FROM (
             SELECT
                 page_type AS `来源页面`,
                 SUM(unique_visitor_count) AS `访客数`,
                 COUNT(*) AS `记录数`
             FROM 09_shop_db.cleaned_pc_data
             WHERE dt = '2025-01-01'
             GROUP BY page_type
         )
) ts
ORDER BY ss.`访客数` DESC
LIMIT 20;
"


ADS_PC_SHOP_OTHER_PAGES_TOP20="
INSERT INTO 09_shop_db.pc_shop_other_pages_top20
SELECT
    ROW_NUMBER() OVER(ORDER BY sub.`访客数` DESC) AS `排名`,
    sub.`TOP引流入口`,
    sub.`访客数`,
    CONCAT(sub.`跳出率`, '%') AS `跳出率`
FROM (
         SELECT
             page_url AS `TOP引流入口`,
             SUM(unique_visitor_count) AS `访客数`,
             ROUND(
                             SUM(CASE WHEN next_page = 'exit' THEN 1 ELSE 0 END)
                             / COUNT(*) * 100,
                             2
                 ) AS `跳出率`
         FROM 09_shop_db.cleaned_pc_data
         WHERE dt = '2025-01-01'
         GROUP BY page_url
     ) sub
ORDER BY sub.`访客数` DESC
LIMIT 20;
"

ADS_PAGE_VISIT_RANKING_RESULT="
INSERT INTO 09_shop_db.page_visit_ranking_result
SELECT
    ROW_NUMBER() OVER(ORDER BY sub.`浏览量` DESC) AS `排名`,
    sub.`访问页面`,
    sub.`浏览量`,
    sub.`访客数`,
    sub.`平均停留时长`
FROM (
         SELECT
             page_url AS `访问页面`,
             COUNT(*) AS `浏览量`,
             SUM(unique_visitor_count) AS `访客数`,
             ROUND(AVG(stay_duration), 2) AS `平均停留时长`
         FROM 09_shop_db.cleaned_pc_data
         WHERE dt = '2025-01-01'
         GROUP BY page_url
     ) sub
ORDER BY sub.`浏览量` DESC
LIMIT 20;
"

ADS_PC_SOURCE_STATS_RESULT="
INSERT INTO 09_shop_db.pc_source_stats_result
SELECT
    ss.`来源`,
    ss.`访客数`,
    CONCAT(ROUND(ss.`访客数` / ts.total_visitors * 100, 2), '%') AS `访客数占比`
FROM (
         SELECT
             page_type AS `来源`,
             SUM(unique_visitor_count) AS `访客数`
         FROM 09_shop_db.cleaned_pc_data
         WHERE dt = '2025-01-01'
         GROUP BY page_type
     ) ss
         CROSS JOIN (
    SELECT
        SUM(`访客数`) AS total_visitors
    FROM (
             SELECT
                 page_type AS `来源`,
                 SUM(unique_visitor_count) AS `访客数`
             FROM 09_shop_db.cleaned_pc_data
             WHERE dt = '2025-01-01'
             GROUP BY page_type
         )
) ts
ORDER BY ss.`访客数` DESC;
"

ADS_PC_DESTINATION_STATS_RESULT="
INSERT INTO 09_shop_db.pc_destination_stats_result
SELECT
    ds.`去向`,
    ds.`访客数`,
    CONCAT(ROUND(ds.`访客数` / ts2.total_visitors * 100, 2), '%') AS `访客数占比`,
    ds.`支付金额`,
    CONCAT(ROUND(ds.`支付金额` / ts2.total_amount * 100, 2), '%') AS `支付金额占比`
FROM (
         SELECT
             page_type AS `去向`,
             SUM(unique_visitor_count) AS `访客数`,
             SUM(CASE WHEN is_order = '1' THEN order_amount ELSE 0 END) AS `支付金额`,
             COUNT(*) AS `总记录数`
         FROM 09_shop_db.cleaned_pc_data
         WHERE dt = '2025-01-01'
         GROUP BY page_type
     ) ds
         CROSS JOIN (
    SELECT
        SUM(`访客数`) AS total_visitors,
        SUM(`支付金额`) AS total_amount
    FROM (
             SELECT
                 page_type AS `去向`,
                 SUM(unique_visitor_count) AS `访客数`,
                 SUM(CASE WHEN is_order = '1' THEN order_amount ELSE 0 END) AS `支付金额`,
                 COUNT(*) AS `总记录数`
             FROM 09_shop_db.cleaned_pc_data
             WHERE dt = '2025-01-01'
             GROUP BY page_type
         )
) ts2
ORDER BY ds.`访客数` DESC;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${ADS_WIRELESS_ENTRY_ANALYSIS_RESULT}${ADS_PAGE_TYPE_STATS_RESULT}${ADS_PAGE_DETAIL_STATS_RESULT}${ADS_SOURCE_STATS_RESULT}${ADS_DESTINATION_STATS_RESULT}${ads_pc_source_analysis_result}${ADS_PC_SHOP_OTHER_PAGES_TOP20}${ADS_PAGE_VISIT_RANKING_RESULT}${ADS_PC_SOURCE_STATS_RESULT}${ADS_PC_DESTINATION_STATS_RESULT}"
